package cn.cvs.dao.sysUser;

import cn.cvs.dao.BaseDao;
import cn.cvs.pojo.SysUser;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * dao层抛出异常，让service层去捕获处理
 * @author Administrator
 *
 */
@Repository
public class SysUserDaoImpl implements SysUserDao {

	@Override
	public int add(Connection connection, SysUser user) throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm = null;
		int updateRows = 0;
		if(null != connection){
			String sql = "insert into t_sys_user (account,realName,password," +
					"roleId,sex,birthday,phone,address,createTime,createUserId) " +
					"values(?,?,?,?,?,?,?,?,?,?)";
			Object[] params = {user.getAccount(),user.getRealName(),user.getPassword(),
							user.getRoleId(),user.getSex(),user.getBirthday(),
							user.getPhone(),user.getAddress(),user.getCreatedTime(),user.getCreatedUserId()};
			updateRows = BaseDao.execute(connection, pstm, sql, params);
			BaseDao.closeResource(null, pstm, null);
		}
		return updateRows;
	}

	@Override
	public SysUser getLoginUser(Connection connection, String account)
			throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm = null;
		ResultSet rs = null;
		SysUser user = null;
		if(null != connection){
			String sql = "select * from t_sys_user where account=?";
			Object[] params = {account};
			rs = BaseDao.execute(connection, pstm, rs, sql, params);
			if(rs.next()){
				user = new SysUser();
				user.setId(rs.getInt("id"));
				user.setAccount(rs.getString("account"));
				user.setRealName(rs.getString("realName"));
				user.setPassword(rs.getString("password"));
				user.setSex(rs.getInt("sex"));
				user.setBirthday(rs.getDate("birthday"));
				user.setPhone(rs.getString("phone"));
				user.setAddress(rs.getString("address"));
				user.setRoleId(rs.getInt("roleId"));
				user.setCreatedUserId(rs.getInt("createdUserId"));
				user.setCreatedTime(rs.getTimestamp("createdTime"));
				user.setUpdatedUserId(rs.getInt("updatedUserId"));
				user.setUpdatedTime(rs.getTimestamp("updatedTime"));
			}
			BaseDao.closeResource(null, pstm, rs);
		}
		return user;
	}

	@Override
	public List<SysUser> selectList(Connection connection, String realName,int roleId,int currentPageNo, int pageSize)
			throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<SysUser> userList = new ArrayList<SysUser>();
		if(connection != null){
			StringBuffer sql = new StringBuffer();
			sql.append("select u.*,r.roleName as roleIdName from t_sys_user u,t_sys_role r where u.roleId = r.id");
			List<Object> list = new ArrayList<Object>();
			/*if(!StringUtils.isNullOrEmpty(realName)){
				sql.append(" and u.realName like ?");
				list.add("%"+realName+"%");
			}*/
			if(roleId > 0){
				sql.append(" and u.roleId = ?");
				list.add(roleId);
			}
			sql.append(" order by createdTime DESC limit ?,?");
			currentPageNo = (currentPageNo-1)*pageSize;
			list.add(currentPageNo);
			list.add(pageSize);

			Object[] params = list.toArray();
			System.out.println("sql ----> " + sql.toString());
			rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
			while(rs.next()){
				SysUser _user = new SysUser();
				_user.setId(rs.getInt("id"));
				_user.setAccount(rs.getString("account"));
				_user.setRealName(rs.getString("realName"));
				_user.setSex(rs.getInt("sex"));
				_user.setBirthday(rs.getDate("birthday"));
				_user.setPhone(rs.getString("phone"));
				_user.setRoleId(rs.getInt("roleId"));
				_user.setRoleIdName(rs.getString("roleIdName"));
				userList.add(_user);
			}
			BaseDao.closeResource(null, pstm, rs);
		}
		return userList;
	}

	@Override
	public int deleteUserById(Connection connection,Integer delId) throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm = null;
		int flag = 0;
		if(null != connection){
			String sql = "delete from t_sys_user where id=?";
			Object[] params = {delId};
			flag = BaseDao.execute(connection, pstm, sql, params);
			BaseDao.closeResource(null, pstm, null);
		}
		return flag;
	}

	@Override
	public SysUser getUserById(Connection connection, String id) throws Exception {
		// TODO Auto-generated method stub
		SysUser user = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		if(null != connection){
			String sql = "select u.*,r.roleName as roleIdName from t_sys_user u,t_sys_role r where u.id=? and u.roleId = r.id";
			Object[] params = {id};
			rs = BaseDao.execute(connection, pstm, rs, sql, params);
			if(rs.next()){
				user = new SysUser();
				user.setId(rs.getInt("id"));
				user.setAccount(rs.getString("account"));
				user.setRealName(rs.getString("realName"));
				user.setPassword(rs.getString("password"));
				user.setSex(rs.getInt("sex"));
				user.setBirthday(rs.getDate("birthday"));
				user.setPhone(rs.getString("phone"));
				user.setAddress(rs.getString("address"));
				user.setRoleId(rs.getInt("roleId"));
				user.setCreatedUserId(rs.getInt("createUserId"));
				user.setCreatedTime(rs.getTimestamp("createdTime"));
				user.setUpdatedUserId(rs.getInt("updatedUserId"));
				user.setUpdatedTime(rs.getTimestamp("updatedTime"));
				user.setRoleIdName(rs.getString("roleIdName"));
			}
			BaseDao.closeResource(null, pstm, rs);
		}
		return user;
	}

	@Override
	public int modify(Connection connection, SysUser user) throws Exception {
		// TODO Auto-generated method stub
		int flag = 0;
		PreparedStatement pstm = null;
		if(null != connection){
			String sql = "update t_sys_user set realName=?,"+
				"sex=?,birthday=?,phone=?,address=?,roleId=?,updatedUserId=?,updatedTime=? where id = ? ";
			Object[] params = {user.getRealName(),user.getSex(),user.getBirthday(),
					user.getPhone(),user.getAddress(),user.getRoleId(),user.getUpdatedUserId(),
					user.getUpdatedTime(),user.getId()};
			flag = BaseDao.execute(connection, pstm, sql, params);
			BaseDao.closeResource(null, pstm, null);
		}
		return flag;
	}

	@Override
	public int updatePwd(Connection connection, int id, String pwd)
			throws Exception {
		// TODO Auto-generated method stub
		int flag = 0;
		PreparedStatement pstm = null;
		if(connection != null){
			String sql = "update t_sys_user set password= ? where id = ?";
			Object[] params = {pwd,id};
			flag = BaseDao.execute(connection, pstm, sql, params);
			BaseDao.closeResource(null, pstm, null);
		}
		return flag;
	}

	@Override
	public int selectCount(Connection connection, String realName, int roleId)
			throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm = null;
		ResultSet rs = null;
		int count = 0;
		if(connection != null){
			StringBuffer sql = new StringBuffer();
			sql.append("select count(1) as count from t_sys_user u,t_sys_role r where u.roleId = r.id");
			List<Object> list = new ArrayList<Object>();
			/*if(!StringUtils.isNullOrEmpty(realName)){
				sql.append(" and u.realName like ?");
				list.add("%"+realName+"%");
			}*/
			if(roleId > 0){
				sql.append(" and u.roleId = ?");
				list.add(roleId);
			}
			Object[] params = list.toArray();
			System.out.println("sql ----> " + sql.toString());
			rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
			if(rs.next()){
				count = rs.getInt("count");
			}
			BaseDao.closeResource(null, pstm, rs);
		}
		return count;
	}

	
}
